﻿Imports System.Data.OleDb
Public Class Class1

    'Takes a number and returns a visual represntation of the rating
    Public Shared Function showRating(ByVal cakeRating As Integer)
        'Function to display the cake Rating as a number of pictures instead of text

        'Declare the String
        Dim strCakeRating As String
        strCakeRating = "" 'default as nothing

        'For each rating add a @ to the String
        While cakeRating > 0
            strCakeRating = strCakeRating & "@"
            cakeRating = cakeRating - 1
        End While

        'Replace the @ sign with the picture of the rating cake
        strCakeRating = strCakeRating.Replace("@", "<img src = Pictures/rating.png width = 25 height = 25>")

        'Return the String to Display
        Return strCakeRating

    End Function

    Public Shared Function getUserNumber(ByVal userName As String)
        Dim userNumber As Integer
        userNumber = 0
        Dim SQL As String = "SELECT UserId FROM aspnet_Users WHERE UserName = @f1"
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("CakeConnection").ConnectionString)
        Dim cmd As OleDbCommand = New OleDbCommand(SQL, conn)
        cmd.Parameters.AddWithValue("@f1", userName)
        conn.Open()
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        dr.Read()
        If dr.HasRows Then
            'Fetch the user number
            userNumber = dr.GetValue(0)
        End If
        conn.Close()
        conn.Dispose()
        Return userNumber

    End Function

    Public Shared Function addTag(ByVal listId, ByVal cakeId)
        Dim mysql As String = "INSERT INTO CakeTagsLink (C_FID,CT_FID) VALUES (@f1,@f2)"
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("CakeConnection").ConnectionString)
        Dim cmd As OleDbCommand = New OleDbCommand(mysql, conn)
        cmd.Parameters.AddWithValue("@f1", cakeId)
        cmd.Parameters.AddWithValue("@f2", listId)
        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()
        cmd.Dispose()
        conn.Dispose()
        Return True
    End Function

    Public Shared Function addNewTag(ByVal tag As String, ByVal cakeId As Integer)
        'Check if tag is already on list (and add if is)
        Dim sql As String = "Select * FROM CakeTags WHERE TagName = @f1"
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("CakeConnection").ConnectionString)
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        cmd.Parameters.AddWithValue("@f1", tag)
        conn.Open()
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        dr.Read()
        If dr.HasRows Then
            'It exists so get value and add
            addTag(dr.GetValue(0), cakeId)
        Else
            'Add to the TagValues then addtag
            Dim sql1 As String = "INSERT INTO CakeTags (TagName) VALUES (@f1)"
            Dim conn1 As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("CakeConnection").ConnectionString)
            Dim cmd1 As OleDbCommand = New OleDbCommand(sql1, conn1)
            cmd1.Parameters.AddWithValue("@f1", tag)
            conn1.Open()
            cmd1.ExecuteNonQuery()
            conn1.Close()
            cmd1.Dispose()
            conn1.Dispose()

            Dim listno As Integer
            Dim sql2 As String = "SELECT MAX(ID) FROM CakeTags"
            Dim conn2 As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("CakeConnection").ConnectionString)
            Dim cmd2 As OleDbCommand = New OleDbCommand(sql2, conn2)
            conn2.Open()
            listno = cmd2.ExecuteScalar()
            conn2.Close()
            cmd2.Dispose()
            conn2.Dispose()

            addTag(listno, cakeId)

        End If
        conn.Close()
        

        Return True
    End Function


    Public Shared Function addRTag(ByVal rid, ByVal cakeId)
        Dim mysql As String = "INSERT INTO RecipeTagLink (C_FID,R_FID) VALUES (@f1,@f2)"
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("CakeConnection").ConnectionString)
        Dim cmd As OleDbCommand = New OleDbCommand(mysql, conn)
        cmd.Parameters.AddWithValue("@f1", cakeId)
        cmd.Parameters.AddWithValue("@f2", rid)
        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()
        cmd.Dispose()
        conn.Dispose()
        Return True
    End Function

    Public Shared Function addNewrTag(ByVal tag As String, ByVal cakeId As Integer)
        'Check if tag is already on list (and add if is)
        Dim sql As String = "Select * FROM RecipeTags WHERE RecipeItem = @f1"
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("CakeConnection").ConnectionString)
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        cmd.Parameters.AddWithValue("@f1", tag)
        conn.Open()
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        dr.Read()
        If dr.HasRows Then
            'It exists so get value and add
            addTag(dr.GetValue(0), cakeId)
        Else
            'Add to the TagValues then addtag
            Dim sql1 As String = "INSERT INTO RecipeTags (RecipeItem) VALUES (@f1)"
            Dim conn1 As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("CakeConnection").ConnectionString)
            Dim cmd1 As OleDbCommand = New OleDbCommand(sql1, conn1)
            cmd1.Parameters.AddWithValue("@f1", tag)
            conn1.Open()
            cmd1.ExecuteNonQuery()
            conn1.Close()
            cmd1.Dispose()
            conn1.Dispose()

            Dim listno As Integer
            Dim sql2 As String = "SELECT MAX(ID) FROM RecipeTags"
            Dim conn2 As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("CakeConnection").ConnectionString)
            Dim cmd2 As OleDbCommand = New OleDbCommand(sql2, conn2)
            conn2.Open()
            listno = cmd2.ExecuteScalar()
            conn2.Close()
            cmd2.Dispose()
            conn2.Dispose()

            addRTag(listno, cakeId)

        End If
        conn.Close()


        Return True
    End Function

    Public Shared Function addCakePoint(ByVal userid As Integer, ByVal points As Integer, ByVal reason As String)

        Dim SQL As String = "INSERT INTO CakePoints (userid,points,reason) VALUES (@f1,@f2,@f3)"
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("CakeConnection").ConnectionString)
        Dim cmd As OleDbCommand = New OleDbCommand(SQL, conn)
        cmd.Parameters.AddWithValue("@f1", userid)
        cmd.Parameters.AddWithValue("@f2", points)
        cmd.Parameters.AddWithValue("@f3", reason)

        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()
        cmd.Dispose()
        conn.Dispose()

        Return True

    End Function

    Public Shared Function ratedCake(ByVal userId As Integer, ByVal cakeId As Integer)
        Dim SQL As String = "SELECT * FROM cakeRating WHERE cakeId = @f1 AND userId = @f2"
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("CakeConnection").ConnectionString)
        Dim cmd As OleDbCommand = New OleDbCommand(SQL, conn)
        cmd.Parameters.AddWithValue("@f1", cakeId)
        cmd.Parameters.AddWithValue("@f2", userId)

        conn.Open()
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        dr.Read()
        If dr.HasRows Then
            Return dr.Item("Rating")
        Else
            Return 0
        End If
        dr.Close()
        conn.Close()
        cmd.Dispose()
        conn.Dispose()

    End Function

    Public Shared Function rateTheCake(ByVal userId As Integer, ByVal cakeId As Integer, ByVal rating As Integer)
        Dim SQL As String
        If ratedCake(userId, cakeId) > 0 Then
            'Update
            SQL = "UPDATE cakeRating SET Rating=@f1 WHERE cakeId = @f2 AND userId = @f3"
            Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("CakeConnection").ConnectionString)
            Dim cmd As OleDbCommand = New OleDbCommand(SQL, conn)
            cmd.Parameters.AddWithValue("@f1", rating)
            cmd.Parameters.AddWithValue("@f2", cakeId)
            cmd.Parameters.AddWithValue("@f3", userId)

            conn.Open()
            cmd.ExecuteNonQuery()
            conn.Close()
            cmd.Dispose()
            conn.Dispose()
        Else
            'Insert
            SQL = "INSERT INTO cakeRating (cakeId,userId,Rating) VALUES (@f1,@f2,@f3)"
            Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("CakeConnection").ConnectionString)
            Dim cmd As OleDbCommand = New OleDbCommand(SQL, conn)
            cmd.Parameters.AddWithValue("@f1", cakeId)
            cmd.Parameters.AddWithValue("@f2", userId)
            cmd.Parameters.AddWithValue("@f3", rating)
            conn.Open()
            cmd.ExecuteNonQuery()
            conn.Close()
            cmd.Dispose()
            conn.Dispose()
        End If
        updatecakeRating(cakeId)
        
        Return True

    End Function

    Public Shared Function updatecakeRating(ByVal cakeid)
        Dim SQL As String = "SELECT [CountOfRating],[SumOfRating] FROM rateCake WHERE cakeId = @f1"
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("CakeConnection").ConnectionString)
        Dim cmd As OleDbCommand = New OleDbCommand(SQL, conn)
        cmd.Parameters.AddWithValue("@f1", cakeid)
        conn.Open()
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        dr.Read()
        If dr.HasRows Then
            'Average all ratings out

            'Get percent of Total
            Dim i As Single = dr.GetValue(1) / (dr.GetValue(0) * 5)
            i = i * 100
            Dim x As Single = 0.05 * i

            Dim newa As Integer = x

            Dim sql1 As String = "Update Cakes SET CakeRating=@f1 WHERE ID=@f2"
            Dim conn1 As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("CakeConnection").ConnectionString)
            Dim cmd1 As OleDbCommand = New OleDbCommand(sql1, conn1)
            cmd1.Parameters.AddWithValue("@f1", newa)
            cmd1.Parameters.AddWithValue("@f2", cakeid)
            conn1.Open()
            cmd1.ExecuteNonQuery()
            conn1.Close()
            cmd1.Dispose()
            conn1.Dispose()
        Else
            'No Cake
        End If
        dr.close()
        conn.Close()
        cmd.Dispose()
        conn.Dispose()
        Return True

    End Function

End Class
